package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;
import cn.gson.crm.model.entity.Student;

public class StudentClassDao {
	private int pageSize = 3;
	
	/**
	 * 获取状态为status
	 * 
	 * @param status
	 * @param stuName
	 * @return
	 * @throws SQLException
	 */
	public List<Student> getStudentList(int page, String status, String isAllocation, String stuName, int classType, int classId)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from student where stu_status = ? and is_allocation = ? and class_type = ? and class_id = ? and grade_id = 5 ";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		params.add(classType);
		params.add(classId);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?";
			params.add("%" + stuName.trim() + "%");
		}
		sql += "order by id desc limit ?,? ";
		params.add(start);
		params.add(pageSize);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<Student> studentList = null;
		return buildStudentList(rows, studentList);
	}
	
	/**
	 * 获取状态为status
	 * 
	 * @param status
	 * @param isAllocation
	 * @param stuName
	 * @param gradeId
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status, String isAllocation, String stuName, int classType, int classId) throws SQLException {
		String sql = "select count(*)total from student where stu_status = ? and is_allocation = ? and class_type = ? and class_id = ? and grade_id = 5 ";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		params.add(classType);
		params.add(classId);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?";
			params.add("%" + stuName.trim() + "%");
		}
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (data != null) {
			count = Integer.parseInt(data.get("total").toString());
		}
		return (int) Math.ceil(count.doubleValue() / pageSize);
	}
	
	/**
	 * 组装学员信息列表
	 * 
	 * @param dataList
	 * @return
	 * @throws SQLException
	 */
	private List<Student> buildStudentList(List<Map<String, Object>> rows, List<Student> stu)
			throws NumberFormatException, SQLException {
		stu = new ArrayList<>();
		if (rows != null) {
			for (Map<String, Object> row : rows) {
				stu.add(buildStudentDetail(row));
			}
		}
		return stu;
	}
	
	/**
	 * 组装学生信息
	 * 
	 * @param data
	 * @return
	 */
	private Student buildStudentDetail(Map<String, Object> data) throws SQLException {
		Student po = null;
		if (data != null) {
			po = new Student();
			if (data.get("id") != null) {
				po.setId(Integer.parseInt(data.get("id").toString()));
			}
			po.setIsAllocation((String) data.get("is_allocation"));
			po.setName((String) data.get("stu_name"));
			po.setGender((String) data.get("stu_gender"));
			po.setSource((String) data.get("stu_source"));
			po.setEducation((String) data.get("stu_education"));
			po.setPhone((String) data.get("stu_phone"));
			po.setAddress((String) data.get("stu_address"));
			po.setIdCard((String) data.get("stu_idcard"));
			po.setQq((String) data.get("stu_qq"));
			po.setFatherName((String) data.get("stu_fathername"));
			po.setFatherTel((String) data.get("stu_fathertel"));
			po.setMotherName((String) data.get("stu_mothername"));
			po.setMotherTel((String) data.get("stu_mothertel"));
			po.setCreator((String) data.get("stu_creator"));

			po.setCreateTime((Date) data.get("stu_createtime"));
			if (data.get("grade_id") != null) {
				po.setGradeId(Integer.parseInt(data.get("grade_id").toString()));
			}
			if (data.get("counselor_id") != null) {
				po.setCounselorId(Integer.parseInt(data.get("counselor_id").toString()));
			}
			po.setStatus((String) data.get("stu_status"));
			po.setIsAllocation((String)data.get("is_allocation"));
			if(data.get("is_allpay") != null){
				po.setIs_allpay(Integer.parseInt(data.get("is_allpay").toString()));
			}
			if(data.get("class_type") != null){
				po.setClassType(Integer.parseInt(data.get("class_type").toString()));
			}
			
			if(data.get("class_id") != null){
				po.setClassId(Integer.parseInt(data.get("class_id").toString()));
			}
		}
		return po;
	}

}
